package com.example;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.sql.*;  // 添加JDBC相关导入

public class DataManager {
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/cinema_system?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "123456"; // 请根据实际情况修改数据库密码
    private static final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    // 移除原文件路径常量，改为数据库表操作

    private static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(JDBC_URL, DB_USER, DB_PASSWORD);
    }

    public static void initDatabase() {
        try (
            // 先连接到MySQL服务器（不指定数据库）
            Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true",
                DB_USER, DB_PASSWORD);
            Statement stmt = conn.createStatement()) {
            // 创建数据库（如果不存在）
            stmt.execute("CREATE DATABASE IF NOT EXISTS cinema_system");
            // 切换到新创建的数据库
            stmt.execute("USE cinema_system");
            
            // 创建用户表
            stmt.execute("CREATE TABLE IF NOT EXISTS users ("
                    + "userId VARCHAR(50) PRIMARY KEY,"
                    + "username VARCHAR(50) NOT NULL,"
                    + "passwordHash VARCHAR(100) NOT NULL,"
                    + "role VARCHAR(20) NOT NULL,"
                    + "mustChangePassword BOOLEAN,"
                    + "registrationTime DATETIME,"
                    + "phoneNumber VARCHAR(20))");
            // 创建场次表
            stmt.execute("CREATE TABLE IF NOT EXISTS screenings ("
                    + "movieTitle VARCHAR(100) NOT NULL,"
                    + "roomName VARCHAR(50) NOT NULL,"
                    + "startTime DATETIME NOT NULL,"
                    + "price DOUBLE,"
                    + "PRIMARY KEY (movieTitle, roomName, startTime))");
            // 创建订单表
            stmt.execute("CREATE TABLE IF NOT EXISTS orders ("
                    + "orderId VARCHAR(50) PRIMARY KEY,"
                    + "username VARCHAR(50) NOT NULL,"
                    + "movieTitle VARCHAR(100) NOT NULL,"
                    + "roomName VARCHAR(50) NOT NULL,"
                    + "startTime DATETIME,"
                    + "`row` INT,"
                    + "seat INT,"
                    + "amount DOUBLE,"
                    + "orderTime DATETIME,"
                    + "isRefunded BOOLEAN,"
                    + "refundTime DATETIME NULL,"   // 添加 NULL 约束
                    + "refundAmount DOUBLE NULL)");  // 添加 NULL 约束
            // 创建日志表
            stmt.execute("CREATE TABLE IF NOT EXISTS logs ("
                    + "logId INT AUTO_INCREMENT PRIMARY KEY,"
                    + "logContent TEXT,"
                    + "logTime DATETIME)");
            // 创建零食表
            stmt.execute("CREATE TABLE IF NOT EXISTS snacks ("
                    + "name VARCHAR(50) PRIMARY KEY,"
                    + "price DOUBLE,"
                    + "stock INT)");
            // 创建电影表（补充）
            stmt.execute("CREATE TABLE IF NOT EXISTS movies ("
                    + "movieId VARCHAR(50) PRIMARY KEY," // 新增唯一标识
                    + "title VARCHAR(100) NOT NULL,"
                    + "director VARCHAR(100) NOT NULL,"
                    + "actors TEXT,"
                    + "synopsis TEXT,"
                    + "duration INT NOT NULL,"
                    + "rating DOUBLE,"
                    + "UNIQUE (title))");
            
            // 检查是否存在用户，若没有则创建admin
            ResultSet userCountRs = stmt.executeQuery("SELECT COUNT(*) AS userCount FROM users");
            if (userCountRs.next() && userCountRs.getInt("userCount") == 0) {
                // 使用MD5Util加密密码
                String encryptedPassword = MD5Util.encrypt("ynuinfo#777");
                // 修正SQL字符串拼接错误（确保引号正确闭合，变量正确嵌入）
                String insertAdminSql = "INSERT INTO users (userId, username, passwordHash, role, mustChangePassword, registrationTime, phoneNumber) " +
                        "VALUES ('0', 'admin', '" + encryptedPassword + "', '管理员', false, NOW(), '1234567890')";
                stmt.executeUpdate(insertAdminSql);
                System.out.println("创建初始管理员用户admin成功");
            }
            System.out.println("数据库初始化完成");
        } catch (SQLException e) {
            System.err.println("数据库初始化失败: " + e.getMessage());
        }
    }
    // 加载用户数据（数据库版）
    public static List<User> loadUsers() {
        List<User> users = new ArrayList<>();
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM users")) {
            while (rs.next()) {
                String userId = rs.getString("userId");
                String username = rs.getString("username");
                String passwordHash = rs.getString("passwordHash");
                String role = rs.getString("role");
                boolean mustChangePassword = rs.getBoolean("mustChangePassword");
                LocalDateTime registrationTime = LocalDateTime.parse(rs.getString("registrationTime"), formatter);
                String phoneNumber = rs.getString("phoneNumber");
                User user = new User(userId, username, passwordHash, role, registrationTime, phoneNumber);
                user.setMustChangePassword(mustChangePassword);
                users.add(user);
            }
        } catch (SQLException e) {
            System.err.println("加载用户数据时出错: " + e.getMessage());
        }
        return users;
    }
    // 保存用户数据（数据库版）
    public static void saveUsers(List<User> users) {
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement("REPLACE INTO users VALUES (?,?,?,?,?,?,?)");) {
            for (User user : users) {
                pstmt.setString(1, user.getUserId());
                pstmt.setString(2, user.getUsername());
                pstmt.setString(3, user.getPasswordHash());
                pstmt.setString(4, user.getRole());
                pstmt.setBoolean(5, user.mustChangePassword());
                pstmt.setString(6, user.getRegistrationTime().format(formatter));
                pstmt.setString(7, user.getPhoneNumber());
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        } catch (SQLException e) {
            System.err.println("保存用户数据时出错: " + e.getMessage());
        }
    }

    // 加载电影数据（数据库版）
    public static List<Movie> loadMovies() {
        List<Movie> movies = new ArrayList<>();
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM movies")) {
            while (rs.next()) {
                String title = rs.getString("title");
                String director = rs.getString("director");
                String actors = rs.getString("actors");
                String synopsis = rs.getString("synopsis");
                int duration = rs.getInt("duration");
                double rating = rs.getDouble("rating");
                movies.add(new Movie(title, director, actors, synopsis, duration, rating));
            }
        } catch (SQLException e) {
            System.err.println("加载电影数据时出错: " + e.getMessage());
        }
        return movies;
    }


    // 保存电影数据（数据库版）
    public static void saveMovies(List<Movie> movies) {
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement("REPLACE INTO movies VALUES (?,?,?,?,?,?)");) {
            for (Movie movie : movies) {
                pstmt.setString(1, movie.getTitle());
                pstmt.setString(2, movie.getDirector());
                pstmt.setString(3, movie.getActors());
                pstmt.setString(4, movie.getSynopsis());
                pstmt.setInt(5, movie.getDuration());
                pstmt.setDouble(6, movie.getRating());
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        } catch (SQLException e) {
            System.err.println("保存电影数据时出错: " + e.getMessage());
        }
    }


    // 加载场次数据（数据库版）
    public static List<Screening> loadScreenings(List<Movie> movies, List<ScreeningRoom> rooms) {
        List<Screening> screenings = new ArrayList<>();
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM screenings")) {
            while (rs.next()) {
                String movieTitle = rs.getString("movieTitle");
                String roomName = rs.getString("roomName");
                LocalDateTime startTime = LocalDateTime.parse(rs.getString("startTime"), formatter);
                double price = rs.getDouble("price");
                Movie movie = findMovieByTitle(movies, movieTitle);
                ScreeningRoom room = findRoomByName(rooms, roomName);
                if (movie != null && room != null) {
                    screenings.add(new Screening(movie, room, startTime, price));
                }
            }
        } catch (SQLException e) {
            System.err.println("加载场次数据时出错: " + e.getMessage());
        }
        return screenings;
    }


    // 保存场次数据（数据库版）
    public static void saveScreenings(List<Screening> screenings) {
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement("REPLACE INTO screenings VALUES (?,?,?,?)");) {
            for (Screening screening : screenings) {
                pstmt.setString(1, screening.getMovie().getTitle());
                pstmt.setString(2, screening.getRoom().getName());
                pstmt.setString(3, screening.getStartTime().format(formatter));
                pstmt.setDouble(4, screening.getPrice());
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        } catch (SQLException e) {
            System.err.println("保存场次数据时出错: " + e.getMessage());
        }
    }


    // 加载订单数据（数据库版）
    public static List<Order> loadOrders() {
        List<Order> orders = new ArrayList<>();
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM orders")) {
            while (rs.next()) {
                String orderId = rs.getString("orderId");
                String username = rs.getString("username");
                String movieTitle = rs.getString("movieTitle");
                String roomName = rs.getString("roomName");
                LocalDateTime startTime = LocalDateTime.parse(rs.getString("startTime"), formatter);
                int row = rs.getInt("row");
                int seat = rs.getInt("seat");
                double amount = rs.getDouble("amount");
                LocalDateTime orderTime = LocalDateTime.parse(rs.getString("orderTime"), formatter);
                boolean isRefunded = rs.getBoolean("isRefunded");
                LocalDateTime refundTime = isRefunded ? LocalDateTime.parse(rs.getString("refundTime"), formatter) : null;
                double refundAmount = isRefunded ? rs.getDouble("refundAmount") : 0;
                Movie movie = findMovieByTitle(CinemaSystem.movies, movieTitle);
                ScreeningRoom room = findRoomByName(CinemaSystem.rooms, roomName);
                if (movie != null && room != null) {
                    Screening screening = new Screening(movie, room, startTime, amount);
                    Order order = new Order(orderId, username, screening, row, seat, amount, orderTime);
                    if (isRefunded) {
                            order.setRefunded(true);
                            order.setRefundTime(refundTime);
                            order.setRefundAmount(refundAmount);
                        }
                    orders.add(order);
                }
            }
        } catch (SQLException e) {
            System.err.println("加载订单数据时出错: " + e.getMessage());
        }
        return orders;
    }


    // 保存订单数据（数据库版）
    public static void saveOrders(List<Order> orders) {
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement("REPLACE INTO orders VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");) {
            for (Order order : orders) {
                pstmt.setString(1, order.getOrderId());
                pstmt.setString(2, order.getUsername());
                pstmt.setString(3, order.getScreening().getMovie().getTitle());
                pstmt.setString(4, order.getScreening().getRoom().getName());
                pstmt.setString(5, order.getScreening().getStartTime().format(formatter));
                pstmt.setInt(6, order.getRow());
                pstmt.setInt(7, order.getSeat());
                pstmt.setDouble(8, order.getAmount());
                pstmt.setString(9, order.getOrderTime().format(formatter));
                pstmt.setBoolean(10, order.isRefunded());
                if (order.isRefunded()) {
                    pstmt.setString(11, order.getRefundTime().format(formatter));
                    pstmt.setDouble(12, order.getRefundAmount());
                } else {
                    pstmt.setNull(11, Types.DATE); // 设置为 NULL
                    pstmt.setDouble(12, 0);
                }
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        } catch (SQLException e) {
            System.err.println("保存订单数据时出错: " + e.getMessage());
        }
    }


    // 从数据库加载历史日志
    public static List<String> loadLogs() {
        List<String> logs = new ArrayList<>();
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT logContent FROM logs ORDER BY logTime DESC")) {
            while (rs.next()) {
                logs.add(rs.getString("logContent"));
            }
        } catch (SQLException e) {
            System.err.println("加载日志时出错: " + e.getMessage());
        }
        return logs;
    }

    private static Movie findMovieByTitle(List<Movie> movies, String title) {
        for (Movie movie : movies) {
            if (movie.getTitle().equals(title)) {
                return movie;
            }
        }
        return null;
    }

    public static void saveLogs(List<String> logs) {
        if (logs.isEmpty()) return;
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement("INSERT INTO logs (logContent, logTime) VALUES (?,?)")) {
            LocalDateTime now = LocalDateTime.now();
            for (String log : logs) {
                pstmt.setString(1, log);
                pstmt.setString(2, now.format(formatter));
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        } catch (SQLException e) {
            System.err.println("保存日志时出错: " + e.getMessage());
        }
    }

    private static ScreeningRoom findRoomByName(List<ScreeningRoom> rooms, String name) {
        for (ScreeningRoom room : rooms) {
            if (room.getName().equals(name)) {
                return room;
            }
        }
        return null;
    }

    // 加载零食数据（数据库版）
    public static List<Snack> loadSnacks() {
        List<Snack> snacks = new ArrayList<>();
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM snacks")) {
            while (rs.next()) {
                snacks.add(new Snack(
                    rs.getString("name"),
                    rs.getDouble("price"),
                    rs.getInt("stock")
                ));
            }
        } catch (SQLException e) {
            System.err.println("加载零食数据失败: " + e.getMessage());
        }
        return snacks;
    }

    
    // 保存零食数据（数据库版）
    public static void saveSnacks(List<Snack> snacks) {
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement("REPLACE INTO snacks VALUES (?,?,?)");) {
            for (Snack snack : snacks) {
                pstmt.setString(1, snack.getName());
                pstmt.setDouble(2, snack.getPrice());
                pstmt.setInt(3, snack.getStock());
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        } catch (SQLException e) {
            System.err.println("保存零食数据失败: " + e.getMessage());
        }
    }

}